CREATE TABLE [dbo].[Country](
	[CountryId] [int] NOT NULL,
	[CountryName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
(
	[CountryId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Country
(CountryId, CountryName)
select 1, 'USA'
union all
select 2, 'Canada'
go
alter table [State]
add CountryId int not null default(1)
go
ALTER TABLE [dbo].[State]  WITH CHECK ADD  CONSTRAINT [FK_State_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([CountryId])
GO
ALTER TABLE [dbo].[State] CHECK CONSTRAINT [FK_State_Country]
GO
insert into [State]
(StateName, StateCode, CountryId)
select 'Ontario', 'Ontario', 2
union all
select 'Quebec', 'Quebec', 2
union all
select 'British Columbia', 'British Columbia', 2
union all
select 'Alberta', 'Alberta', 2
union all
select 'Manitoba', 'Manitoba', 2
union all
select 'Saskatchewan', 'Saskatchewan', 2
union all
select 'Nova Scotia', 'Nova Scotia', 2



alter table Contact
add CountryId int not null default(1)
go
ALTER TABLE [dbo].[Contact]  WITH CHECK ADD  CONSTRAINT [FK_Contact_Country] FOREIGN KEY([CountryId])
REFERENCES [dbo].[Country] ([CountryId])
GO
ALTER TABLE [dbo].[Contact] CHECK CONSTRAINT [FK_Contact_Country]
GO



